IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditPackageExec]') AND type in (N'U'))
BEGIN
     DROP TABLE [dbo].[AuditPackageExec]
END
GO

CREATE TABLE [dbo].[AuditPackageExec](
     [AuditPackageExecId]      [int]          NOT NULL,
     [AuditPackageEnabledFlag] [tinyint]      NOT NULL DEFAULT ((1)),
     [AuditPackageName]        [varchar](128) NOT NULL,
     [AuditPackageLocation]    [varchar](30)  NOT NULL,
     [AuditPackagePath]        [varchar](255) NOT NULL,
     [EveryExecFlag]           [tinyint]      NOT NULL DEFAULT ((0)),
     [DailyExecFlag]           [tinyint]      NOT NULL DEFAULT ((0)),
     [MonthlyExecFlag]         [tinyint]      NOT NULL DEFAULT ((0)),
     [QuarterlyExecFlag]       [tinyint]      NOT NULL DEFAULT ((0)),
     CONSTRAINT [PK_AuditPackageExec] PRIMARY KEY CLUSTERED ( [AuditPackageExecId] ASC )
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique identifier of a particular Package Execution, also defines the order in which packages are executed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AuditPackageExec', @level2type=N'COLUMN',@level2name=N'AuditPackageExecId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag indicating whether this package execution is enabled.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AuditPackageExec', @level2type=N'COLUMN',@level2name=N'AuditPackageEnabledFlag'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Package name not including *.dtsx.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AuditPackageExec', @level2type=N'COLUMN',@level2name=N'AuditPackageName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Physical location of package, "FILE SYSTEM" or "SQL SERVER".' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AuditPackageExec', @level2type=N'COLUMN',@level2name=N'AuditPackageLocation'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Path to the package, either file system or SQL server.  Includes full path, including *.dtsx for file system paths.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AuditPackageExec', @level2type=N'COLUMN',@level2name=N'AuditPackagePath'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag indicating that this package should be run everytime the master package executor is run.  Regardless of other flags.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AuditPackageExec', @level2type=N'COLUMN',@level2name=N'EveryExecFlag'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag indicating that this package should be run daily.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AuditPackageExec', @level2type=N'COLUMN',@level2name=N'DailyExecFlag'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag indicating that this package should be run monthly.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AuditPackageExec', @level2type=N'COLUMN',@level2name=N'MonthlyExecFlag'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag indicating that this package should be run quarterly.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AuditPackageExec', @level2type=N'COLUMN',@level2name=N'QuarterlyExecFlag'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Data driven audit package execution control table.  Defines what packages are to be run for auditing and in what order.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AuditPackageExec'
GO
